

import pymssql  #  2.2.11
# pip install pymssql -i https://pypi.tuna.tsinghua.edu.cn/simple
# import _mssql


# server = r'192.168.12.89'  # 连接服务器地址
# user = "ass" # 连接帐号
# password = "123" # 连接密码
# database = 'test'
#
# # conn = pymssql.connect(
# #     host=r'LEIWENYI\SQLEXPRESS',
# #     user=r'LEIWENYI\Dell',
# #     password='12345678',
# #     database='MyDatabase1'
# # )
#
# conn = pymssql.connect(server, user, password, database, login_timeout=10)  #获取连接
#
# cursor = conn.cursor() # 获取光标
#
# # 创建表
# cursor.execute("""
# IF OBJECT_ID('persons', 'U') IS NOT NULL
#     DROP TABLE persons
# CREATE TABLE persons (
#     id INT NOT NULL,
#     name VARCHAR(100),
#     salesrep VARCHAR(100),
#     PRIMARY KEY(id)
# )
# """)
# # cursor.executemany(
# #     "INSERT INTO test_table VALUES (%d, %s, %s)",
# #     [('6', 'John Smith', 'John Doe'),
# #      ('7', 'Jane Doe', 'Joe Dog'),
# #      ('8', 'Mike T.', 'Sarah H.')])
# # 插入多行数据
# # cursor.executemany(
# #     "INSERT INTO persons VALUES (%d, %s, %s)",
# #     [(1, 'John Smith', 'John Doe'),
# #      (2, 'Jane Doe', 'Joe Dog'),
# #      (3, 'Mike T.', 'Sarah H.')])
# # 你必须调用 commit() 来保持你数据的提交如果你没有将自动提交设置为true
# conn.commit()
#
# # 查询数据
# cursor.execute('SELECT * FROM dbo.Customers')
#
# # 遍历数据（存放到元组中） 方式1
# row = cursor.fetchone()
# while row:
#     print("ID=%d, Name=%s" % (row[0], row[1]))
#     row = cursor.fetchone()
# # 遍历数据（存放到元组中） 方式2
# for row in cursor:
#     print('row = %r' % (row,))
#
#
# # 遍历数据（存放到字典中）
# # cursor = conn.cursor(as_dict=True)
# #
# # cursor.execute('SELECT * FROM persons WHERE salesrep=%s', 'John Doe')
# # for row in cursor:
# #     print("ID=%d, Name=%s" % (row['id'], row['name']))
# #
# # conn.close()
# # 关闭连接
# conn.close()
#
# # 注：在任何时候，在一个连接下，一次正在执行的数据库操作只会出现一个cursor对象


class sql_server_client:

    def __init__(self,server = r'192.168.12.89', user = "ass",password = "123", database = 'KittingDB'):
        self.server = server  # 连接服务器地址 ch71w0120  199.63.152.66
        self.user = user  # 连接帐号  MdcsSzPr-rw
        self.password = password # 连接密码 X7tu5#99RT1
        self.database = database # LineKitting

    def connect(self, database=''):

        # if database != '':
        #     self.database = database
        self.conn = pymssql.connect(self.server, self.user, self.password, self.database )  # 获取连接
        self.cursor = self.conn.cursor(as_dict=True) # 获取光标, 字典格式
        print(f'sqlserver connection:  {self.server} {self.user} {self.password} {self.database}')

    def select(self, sql_query_str = 'SELECT * FROM dbo.Kitting_newunittype'):

        self.connect()
        print(f'sql_query_str: {sql_query_str}')
        self.cursor.execute(sql_query_str)
        row = self.cursor.fetchall() # [(),()] or [{}, {}]

        # # 获取查询结果集中的列名
        # column_names = [column[0] for column in self.cursor.description]
        #
        # res = {}
        # for ind, col_name in enumerate(column_names):
        #     res[col_name] =

        self.close()

        return row

    def insert(self, sql_insert_str = '', values_list=[]):
        try:
            # # 定义要插入的数据
            # data_to_insert = ('John Doe', 'john@example.com', '1234567890')
            # # 构造INSERT语句
            # insert_query = "INSERT INTO table_name (column1, column2, column3) VALUES (%s, %s, %s)"

            # 执行插入操作
            self.cursor.executemany(sql_insert_str, values_list)
            # 提交事务
            self.conn.commit()

            print("成功插入记录！")
        except Exception as e:
            # 发生错误时回滚事务
            self.conn.rollback()
            print("插入失败：", str(e))
        # finally:
        #     # 关闭数据库连接
        #     self.cursor.close()
        #     self.conn.close()

    def update(self,sql_update_str):
        try:
            # 执行更新操作
            # self.cursor.execute(query, params)
            self.cursor.execute(sql_update_str)
            # 提交事务（在非自动提交模式下）
            self.conn.commit()
            print("数据更新成功")
        except pymssql.DatabaseError as e:
            # 如果出现数据库错误，回滚事务并打印错误信息
            self.conn.rollback()
            print(f"数据更新失败: {e}")

    def delete(self, sql_delete_str):
        try:
            # 执行删除操作
            # cursor.execute(query, params)
            self.cursor.execute(sql_delete_str)

            # 提交事务（在非自动提交模式下）
            self.conn.commit()
            print("数据删除成功")

        except pymssql.DatabaseError as e:
            # 如果出现数据库错误，回滚事务并打印错误信息
            self.conn.rollback()
            print(f"数据删除失败: {e}")
    def close(self):
        print('sqlserver close')
        self.cursor.close()
        self.conn.close()

def test_sql_server_client():
    # ssclient = sql_server_client(server='ch71w0120', user='MdcsSzPr-rw',
    #                              password='X7tu5#99RT1', database='LineKitting')
    ssclient = sql_server_client(server='199.63.152.27', user='MdcsSzPr-rw',
                                 password='X7tu5#99RT1', database='MDCSPr')
    # ssclient.connect()

    # sql_insert_str = \
    #     ("INSERT INTO dbo.Kitting_User "
    #      "([F_Id], [F_UserName],[F_Password],[SecurityRight]) "
    #      "VALUES (%s, %s, %s, %s)")
    # values_list = (('3','kit_user3', '123', 'sr1'),
    #                ('5','kit_user2', '123', 'sr2'))
    # ssclient.insert(sql_insert_str,values_list) # 插入


    # sql_update_str = "UPDATE [KittingDB].[dbo].[Kitting_User] SET [F_Password]='12345' WHERE [F_UserName]='KIT_USER3'"
    # ssclient.update(sql_update_str)
    #
    # sql_delete_str = "DELETE FROM [KittingDB].[dbo].[Kitting_User] WHERE [F_UserName]='KIT_USER3'"
    # ssclient.delete(sql_delete_str)

    sql_query_str = 'SELECT * FROM Kitting_User'
    print(sql_query_str)
    row = ssclient.select(sql_query_str) # 查询
    for ro in row:
        print(ro)

    ssclient.close()

if __name__ =='__main__':
    test_sql_server_client()